If DBMS authentication, using passwords, is employed, SQL Server must enforce the DoD standards for password complexity and lifetime.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-79191	SQL6-D0-007900	SV-93897r1_rule		Medium

Description
OS/enterprise authentication and identification must be used (SQL2-00-023600). Native DBMS authentication may be used only when circumstances make it unavoidable; and must be documented and AO-approved. The DoD standard for authentication is DoD-approved PKI certificates. Authentication based on User ID and Password may be used only when it is not possible to employ a PKI certificate, and requires AO approval. In such cases, the DoD standards for password complexity and lifetime must be implemented. DBMS products that can inherit the rules for these from the operating system or access control program (e.g., Microsoft Active Directory) must be configured to do so. For other DBMSs, the rules must be enforced using available configuration parameters or custom code.

Description

OS/enterprise authentication and identification must be used (SQL2-00-023600). Native DBMS authentication may be used only when circumstances make it unavoidable; and must be documented and AO-approved. The DoD standard for authentication is DoD-approved PKI certificates. Authentication based on User ID and Password may be used only when it is not possible to employ a PKI certificate, and requires AO approval. In such cases, the DoD standards for password complexity and lifetime must be implemented. DBMS products that can inherit the rules for these from the operating system or access control program (e.g., Microsoft Active Directory) must be configured to do so. For other DBMSs, the rules must be enforced using available configuration parameters or custom code.

STIG	Date
MS SQL Server 2016 Instance Security Technical Implementation Guide	2018-03-09

Details

Check Text ( C-78783r1_chk )
Check for use of SQL Server Authentication: SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'SQL Server Authentication' END as [Authentication Mode] SQL Server should be configured to inherit password complexity and password lifetime rules from the operating system. If SQL Server Authentication is enabled and "enforce password policy" and "enforce password expiration" is not employed, this is a finding. Risk must be accepted by the ISSO/ISSM. Review SQL Server to ensure logins are created with respect to the complexity settings. Ensure SQL Logins meet the required settings: SELECT * FROM sys.sql_logins WHERE is_policy_checked <> 1 and is_expiration_checked <> 1 If any records are returned, this is a finding. Review the Operating System settings relating to password complexity. Determine whether the following rules are enforced. If any are not, this is a finding. Check the server operating system for password complexity: Navigate to Start >> All Programs >> Administrative Tools >> Local Security Policy and to review the local policies on the machine. Account Policy >> Password Policy: Ensure the DISA Windows Password Policy is set on the SQL Server member server.

Check Text ( C-78783r1_chk )

Check for use of SQL Server Authentication:

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'SQL Server Authentication' END as [Authentication Mode]

SQL Server should be configured to inherit password complexity and password lifetime rules from the operating system.

If SQL Server Authentication is enabled and "enforce password policy" and "enforce password expiration" is not employed, this is a finding.

Risk must be accepted by the ISSO/ISSM.

Review SQL Server to ensure logins are created with respect to the complexity settings. Ensure SQL Logins meet the required settings:

SELECT * FROM sys.sql_logins WHERE is_policy_checked <> 1 and is_expiration_checked <> 1

If any records are returned, this is a finding.

Review the Operating System settings relating to password complexity.

Determine whether the following rules are enforced. If any are not, this is a finding.

Check the server operating system for password complexity:
Navigate to Start >> All Programs >> Administrative Tools >> Local Security Policy and to review the local policies on the machine. Account Policy >> Password Policy:

Ensure the DISA Windows Password Policy is set on the SQL Server member server.

Fix Text (F-85943r1_fix)
Configure the SQL Server operating system and SQL Server logins for compliance. 1. Ensure the DISA Windows Server 2012/2012 R2 Domain Controller Security Technical Implementation Guide password complexity guidelines are met. 2. Ensure SQL Server is configured to inherit password complexity rules from the operating system for SQL logins. Ensure check of policy and expiration are enforced when SQL logins are created. CREATE LOGIN [] WITH PASSWORD=N'', CHECK_EXPIRATION=ON, CHECK_POLICY=ON

Fix Text (F-85943r1_fix)

Configure the SQL Server operating system and SQL Server logins for compliance.

1. Ensure the DISA Windows Server 2012/2012 R2 Domain Controller Security Technical Implementation Guide password complexity guidelines are met.

2. Ensure SQL Server is configured to inherit password complexity rules from the operating system for SQL logins. Ensure check of policy and expiration are enforced when SQL logins are created.

CREATE LOGIN [] WITH PASSWORD=N'', CHECK_EXPIRATION=ON, CHECK_POLICY=ON